BEGIN;

CREATE TABLE reservation (
  id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  object_state VARCHAR(30) NOT NULL,
  sid VARCHAR(32) NOT NULL,
  school_id INT(11) NOT NULL,
  event_id INT(11) NOT NULL,
  contact_person_id INT(11) NOT NULL,
  FOREIGN KEY (school_id) REFERENCES school(id) ON DELETE CASCADE,
  FOREIGN KEY (event_id) REFERENCES event(id) ON DELETE CASCADE,
  FOREIGN KEY (contact_person_id) REFERENCES contact_person(id) ON DELETE NO ACTION,
  UNIQUE (sid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE reservation ADD INDEX reservation_sid_idx (sid);
ALTER TABLE reservation ADD INDEX reservation_object_state_idx (object_state);
ALTER TABLE reservation ADD INDEX reservation_event_idx (event_id);
ALTER TABLE reservation ADD INDEX reservation_school_idx (school_id);

COMMIT;